package com.appcenter.application.mapper;

import com.appcenter.application.dto.externalapi.BusinessSystemOrgDto;
import com.appcenter.application.dto.orgs.OrgDto;
import com.appcenter.application.input.user.GetUsersInput;
import com.appcenter.application.model.Orgs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

@org.apache.ibatis.annotations.Mapper
@Repository
public interface OrgsMapper extends Mapper<Orgs> {

    //查询当前应用下最大排序值
    @Select("select * FROM orgs WHERE ValidStatus=1 and ParentId=#{ParentId} ORDER BY ListOrder DESC limit 0,1")
    public Orgs maxListOrder(String ParentId);

    @Select("SELECT * from  orgs WHERE ValidStatus=1 and OrgCode=#{orgCode} AND Appcode is #{appCode}")
    public List<Orgs> getOrgsByOrgCode(GetUsersInput input);

    @Select("select b.id,b.JobCode as 'OrgCode',b.JobName as 'OrgName',c.id as 'ParentId',CONCAT(c.OrgIdPath,'/',b.id) as 'OrgIdPath',CONCAT(c.OrgCodePath,'/',b.JobCode) as 'OrgCodePath',CONCAT(c.OrgNamePath,'/',b.JobName) as 'OrgNamePath',(c.Level+1) as 'Level','JZ' as 'AppCode',0 as 'ListOrder',null as 'CreateTime','jim' AS 'CreateUserId',1 AS 'ValidStatus',b.VersionNo as 'VersionNo','岗位' as 'Genre' from orgjobs a" +
            " inner join jobs b on a.JobId=b.id" +
            " inner join orgs c on a.orgid=c.id" +
            " where a.VersionNo=#{versionNo} and b.VersionNo=#{versionNo} and c.VersionNo=#{versionNo} and b.ValidStatus=1 and b.Deleted=0 and c.ValidStatus=1")
    public  List<Orgs> getJobConversionOrg(@Param("versionNo")Long versionNo);

    @Select("select o.* from  orgs o join orgjobs oj on oj.OrgId=o.Id and o.ValidStatus=1 "+
            "join jobs j on j.Id=oj.JobId and j.Deleted=0 "+
            "join jobcategorys  jt on jt.id=j.JobCategoryId and jt.ValidStatus=1 and jt.Deleted=0 "+
            "join dataversion d on d.VersionNo=j.VersionNo "+
            "where oj.VersionNo=d.VersionNo and j.VersionNo=d.VersionNo and o.VersionNo=d.VersionNo "+
            "and j.JobCode=#{jobCode} order by `Level`, o.ListOrder asc")
    public List<OrgDto> getOrgsByCode(Map<String,Object> param);


    @Select("select o.* from  orgs o join orgjobs oj on oj.OrgId=o.Id and o.ValidStatus=1 "+
            "join jobs j on j.Id=oj.JobId and j.Deleted=0 "+
            "join jobcategorys  jt on jt.id=j.JobCategoryId and jt.ValidStatus=1 and jt.Deleted=0 "+
            "join dataversion d on d.VersionNo=j.VersionNo "+
            "where oj.VersionNo=d.VersionNo and j.VersionNo=d.VersionNo and j.VersionNo=d.VersionNo "+
            "and j.JobCode=#{jobCode} order by `Level`, o.ListOrder asc")
    public List<BusinessSystemOrgDto> getBusinessSystemOrgsByJobCode(Map<String,Object> param);

    @Select({
            "<script>",
            "select DISTINCT b.* from orgs a ",
            "inner join orgs b on a.OrgIdPath like concat(b.OrgIdPath,'%')",
            "inner join dataversion d on d.VersionNo=b.VersionNo ",
            "where a.id in",
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "and b.ValidStatus=1 order by b.`Level`",
            "</script>"
    })
    List<Orgs> getUserByIds(@Param("ids") List<String> ids);









    @Select({"<script>" +
            "select * from orgs " +
            "where VersionNo=#{versionNo} and ValidStatus=true " +
            "" +
            " <when test='parentId!=null'>" +
            " and OrgIdPath like concat((select OrgIdPath from orgs where VersionNo=#{versionNo} and ValidStatus=true and id=#{parentId}),'%')" +
            " </when>" +
            "" +
            " <when test='appCode!=null'>" +
            " and appCode=#{appCode}" +
            " </when>" +
            " order by Level,listOrder" +
            "" +
            "</script>"})
    List<Orgs> getOrgs(@Param("parentId") String parentId,@Param("appCode") String appCode,@Param("versionNo") Long versionNo);






    @Select("<script>" +
            "select * from (select NO,Id,OrgCode,OrgName,ParentId,OrgIdPath,OrgCodePath,OrgNamePath,Level,AppCode,ListOrder,CreateTime,CreateUserId,ValidStatus,VersionNo,Genre from orgs " +
            "where VersionNo=#{versionNo} and ValidStatus=true " +
            "" +
            " <when test='parentId!=null'>" +
            " and OrgIdPath like concat((select OrgIdPath from orgs where VersionNo=#{versionNo} and ValidStatus=true and id=#{parentId}),'%')" +
            " </when>" +
            "" +
            " <when test='appCode!=null'>" +
            " and appCode=#{appCode}" +
            " </when>" +
            "union all " +
            "select 1 as 'No',b.id,b.JobCode as 'OrgCode',b.JobName as 'OrgName',c.id as 'ParentId',CONCAT(c.OrgIdPath,'/',b.id) as 'OrgIdPath',CONCAT(c.OrgCodePath, " +
            "'/',b.JobCode) as 'OrgCodePath',CONCAT(c.OrgNamePath,'/',b.JobName) as 'OrgNamePath',(c.Level+1) as 'Level','JZ' as 'AppCode',0 as 'ListOrder',null as " +
            "'CreateTime','jim' AS 'CreateUserId',1 AS 'ValidStatus',b.VersionNo as 'VersionNo','岗位' as 'Genre' from orgjobs a " +
            "inner join jobs b on a.JobId=b.id " +
            "inner join orgs c on a.orgid=c.id " +
            "where a.VersionNo=#{versionNo} and b.VersionNo=#{versionNo} and c.VersionNo=#{versionNo} and b.ValidStatus=1 and b.Deleted=0 and c.ValidStatus=1 " +
            "" +
            " <when test='parentId!=null'>" +
            " and OrgIdPath like concat((select OrgIdPath from orgs where VersionNo=#{versionNo} and ValidStatus=true and id=#{parentId}),'%')" +
            " </when>" +
            "" +
            ") temp order by Level,listOrder " +
            "" +
            "</script>")
    List<Orgs> getOrgJobs(@Param("parentId") String parentId,@Param("appCode") String appCode,@Param("versionNo") Long versionNo);
}
